#!/usr/bin/env python3

import logging, os.path, paramiko, time, re, pymysql
from datetime import datetime

# 1.创建一个logger
logger = logging.getLogger()
logger.setLevel(logging.INFO)  # Log等级总开关,默认level是WARNING,低于该级别的就不输出了,级别排序: CRITICAL > ERROR > WARNING > INFO > DEBUG

# 2.创建一个handler，用于写入日志文件
"""
time.time():从1970年1月1日0分0时0秒起至当前时间，经过的浮点数
time.localtime()：转换为time.struct_time类型的对象的秒数
time.strftime() 函数根据指定的格式把一个时间字符串解析为时间元组
"""
log_time = time.strftime('%Y_%m_%d-%H_%M_%S', time.localtime(time.time()))
log_path = os.path.dirname(os.getcwd())   # os.getcwd() 用于返回当前工作目录
log_name = log_path + log_time + '.log'
log_file = log_name
fh = logging.FileHandler(log_file, mode='w')
fh.setLevel(logging.INFO)  # 输出到file的log等级的开关
# output to console
ch = logging.StreamHandler()
ch.setLevel(logging.WARNING)

# 3.定义handler的输出格式
formatter = logging.Formatter("%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s")
fh.setFormatter(formatter)

# 4.将logger添加到handler里面
logger.addHandler(fh)
# output to console
ch.setFormatter(formatter)
logger.addHandler(ch)

def mysql_cisco_ssh2_log(ip, port, username, password, cmd_list, enable='', verbose=True):
    try:
        ssh = paramiko.SSHClient()
        ssh.load_system_host_keys()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh.connect(ip, port, username, password, timeout=3, compress=True)
        chan = ssh.invoke_shell()
        time.sleep(1)
        x = chan.recv(2048).decode()
        logging.warning('%s connect success ' % ip)

        if enable and '>' in x:
            chan.send('enable'.encode() + b'\n')
            time.sleep(1)
            chan.send(enable.encode() + b'\n')
            time.sleep(1)
        elif not enable and '>' in x:
            print('need enable password')
            return


        date_timer = datetime.now().strftime('%Y_%m_%d')
        mysqldb_str = 'mysql' + date_timer + 'db'

        print('=== mysql init... ===')
        time.sleep(1)
        try:
            conn = pymysql.connect(host='10.1.1.51', port=3306, user='admin', password='admin0123')
            print(f'Connect  host successfully')
            cur = conn.cursor()

            cur.execute(f"CREATE DATABASE {mysqldb_str}")
            conn.commit()
            print(f'The {mysqldb_str} created successfully')

            cur.close()
            conn.close()
        except Exception as e:
            print(f'Creating {mysqldb_str} failed ', e)

        time.sleep(1)
        try:
            conn = pymysql.connect(host='10.1.1.51', port=3306, user='admin', password='admin0123', db=f'{mysqldb_str}')
            print(f'Connect {mysqldb_str} successfully')
            cur = conn.cursor()

            cur.execute("""
            CREATE TABLE `sw_cisco_main_tb` (
              `ipaddr` VARCHAR(15) NOT NULL COMMENT 'host ip address',
              `hostname` VARCHAR(64) NOT NULL DEFAULT 'null' COMMENT 'device name',
              `model` VARCHAR(24) NOT NULL DEFAULT 'null' COMMENT 'device model',
              `sn` VARCHAR(12) NOT NULL DEFAULT 'null' COMMENT 'serial number',
              `mac` VARCHAR(18) NOT NULL DEFAULT 'null' COMMENT 'mac address',
              PRIMARY KEY (`ipaddr`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
            """)
            conn.commit()
            print(f'The sw_cisco_main_tb of {mysqldb_str} created successfully')

            cur.execute("""
            CREATE TABLE `sw_cisco_if_tb` (
              `interface` VARCHAR(16) NOT NULL DEFAULT 'null' COMMENT 'device interface',
              `type` VARCHAR(6) NOT NULL DEFAULT 'null' COMMENT 'interface type A or T',
              `host` VARCHAR(15) NOT NULL COMMENT 'host ip address',
              KEY `host` (`host`),
              CONSTRAINT `sw_cisco_if_tb_ibfk_1` FOREIGN KEY (`host`) REFERENCES `sw_cisco_main_tb` (`ipaddr`) ON DELETE CASCADE ON UPDATE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
            """)
            conn.commit()
            print(f'The sw_cisco_if_tb of {mysqldb_str} created successfully')

            cur.close()
            conn.close()
        except Exception as e:
            print(f'Creating tables of {mysqldb_str} failed ', e)


        # cisco and h3c diff
        chan.send('terminal length 0'.encode() + b'\n')
        time.sleep(1)
        chan.send('terminal no monitor'.encode() + b'\n')
        time.sleep(1)


        # cisco diff
        chan.send('show version'.encode() + b'\n')
        time.sleep(2)
        rackreply1 = chan.recv(40960).decode()
        if verbose:
            # print(rackreply1)

            if 'Cisco IOS Software' in rackreply1:
                print(f'The system of {ip} is IOS, loading......')
                ### SW主机名
                chan.send('show run | in hostname'.encode() + b'\n')
                time.sleep(4)
                x = chan.recv(40960).decode()
                if verbose:
                    # print(x)
                    hostname_str = re.findall(r'hostname (\S+)', x)[0]
                    print(f'{ip} hostname is:', hostname_str)

                    ### SW版本信息等等
                    chan.send('show version'.encode() + b'\n')
                    time.sleep(3)
                    x = chan.recv(40960).decode()
                    if verbose:
                        # print(x)
                        model_str = re.findall(r'Model number\s+:\s*(\S+)', x)[0]
                        print(f'{ip} model is:', model_str)
                        sn_str = re.findall(r'System serial number\s+:\s*(\S+)', x)[0]
                        print(f'{ip} sn is:', sn_str)
                        mac_str = re.findall(r'Base ethernet MAC Address\s+:\s*(\S+)', x)[0]
                        print(f'{ip} mac is:', mac_str)

                        print('=== mysql insert sw_cisco_main_tb... ===')
                        time.sleep(1)
                        try:
                            conn = pymysql.connect(host='10.1.1.51', port=3306, user='admin', password='admin0123', db=f'{mysqldb_str}')
                            print(f'Connect {mysqldb_str} successfully')
                            cur = conn.cursor()
                            cur.execute(
                                "INSERT INTO sw_cisco_main_tb (ipaddr, hostname, model, sn, mac) VALUES ('%s','%s','%s','%s','%s')" %
                                (ip, hostname_str, model_str, sn_str, mac_str)
                            )
                            conn.commit()
                            print(f'Insert the sw_cisco_main_tb of {mysqldb_str} successfully')

                        except Exception as e:
                            print(f'Failed to insert sw_cisco_main_tb of {mysqldb_str} ', e)

                ### SW接口信息
                chan.send('show int status'.encode() + b'\n')
                time.sleep(2)
                x = chan.recv(40960).decode()
                if verbose:
                    ifs_strs = re.findall(r'(\S+)\s.*(connected|notconnect|disabled)\s+(\S+)\s.*', x)
                    # print(ifs_strs)
                    for i in ifs_strs:
                        # print(i[0])
                        # print(i[2])

                        print('=== mysql insert sw_cisco_if_tb... ===')
                        try:
                            conn = pymysql.connect(host='10.1.1.51', port=3306, user='admin', password='admin0123', db=f'{mysqldb_str}')
                            print(f'Connect {mysqldb_str} successfully')
                            cur = conn.cursor()
                            cur.execute(
                                "INSERT INTO sw_cisco_if_tb (interface, type, host) VALUES ('%s','%s','%s')" %
                                (i[0], i[2], ip)
                            )
                            conn.commit()
                            print(f'Insert the sw_cisco_if_tb of {mysqldb_str} successfully')

                            conn.close()

                        except Exception as e:
                            print(f'Failed to insert sw_cisco_if_tb of {mysqldb_str} ', e)
                        ### === mysql end ===


            elif 'Cisco Nexus Operating System (NX-OS) Software' in rackreply1:
                print(f'The system of {ip} is NXOS, loading......')
                ### SW主机名
                chan.send('show run | in hostname'.encode() + b'\n')
                time.sleep(4)
                x = chan.recv(40960).decode()
                if verbose:
                    # print(x)
                    hostname_str = re.findall(r'hostname (\S+)', x)[0]
                    print(f'{ip} hostname is:', hostname_str)
                    ### SW版本信息等等
                    chan.send('show inventory'.encode() + b'\n')
                    time.sleep(3)
                    x = chan.recv(40960).decode()
                    if verbose:
                        # print(x)
                        model_str = re.findall(r'PID:\s*(\S+).*', x)[0]
                        print(f'{ip} model is:', model_str)
                        sn_str = re.findall(r'.*SN:\s*(\S+)\s+', x)[0]
                        print(f'{ip} sn is:', sn_str)

                        print('=== mysql insert sw_cisco_main_tb... ===')
                        time.sleep(1)
                        try:
                            conn = pymysql.connect(host='10.1.1.51', port=3306, user='admin', password='admin0123', db=f'{mysqldb_str}')
                            print(f'Connect {mysqldb_str} successfully')
                            cur = conn.cursor()
                            cur.execute(
                                "INSERT INTO sw_cisco_main_tb (ipaddr, hostname, model, sn, mac) VALUES ('%s','%s','%s','%s','%s')" %
                                (ip, hostname_str, model_str, sn_str, 'null')
                            )
                            conn.commit()
                            print(f'Insert the sw_cisco_main_tb of {mysqldb_str} successfully')

                        except Exception as e:
                            print(f'Failed to insert sw_cisco_main_tb of {mysqldb_str} ', e)

                ### SW接口信息
                chan.send('show int status'.encode() + b'\n')
                time.sleep(2)
                x = chan.recv(40960).decode()
                if verbose:
                    ifs_strs = re.findall(r'(\S+)\s.*(connected|notconnec|xcvrAbsen|down)\s+(\S+)\s.*', x)
                    # print(ifs_strs)
                    for i in ifs_strs:
                        # print(i[0])
                        # print(i[2]) 

                        print('=== mysql insert sw_cisco_if_tb... ===')
                        try:
                            conn = pymysql.connect(host='10.1.1.51', port=3306, user='admin', password='admin0123', db=f'{mysqldb_str}')
                            print(f'Connect {mysqldb_str} successfully')
                            cur = conn.cursor()
                            cur.execute(
                                "INSERT INTO sw_cisco_if_tb (interface, type, host) VALUES ('%s','%s','%s')" %
                                (i[0], i[2], ip)
                            )
                            conn.commit()
                            print(f'Insert the sw_cisco_if_tb of {mysqldb_str} successfully')

                            conn.close()

                        except Exception as e:
                            print(f'Failed to insert sw_cisco_if_tb of {mysqldb_str} ', e)
                        ### === mysql end ===


            else:
                print(f'The system of {ip} is unknown, ending......')

        chan.close()
        ssh.close()
        logging.warning(f'The job of {ip} is over, start next...')

    except Exception as e:
        print(f'{ip} connect failed ', e)
        logging.warning('%s connect failed ' % ip)

if __name__ == '__main__':
    import all_authen
    username = all_authen.login_user
    password = all_authen.login_token

    ip_list = open('mysql_ips.txt', 'r')
    for ip in ip_list:
        mysql_cisco_ssh2_log(ip.strip(), 22, username, password, ['show clock'], enable='cisco', verbose=True)
